package org.example.dao;

import org.example.pojo.Publish;
import org.example.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

// 查看文章  部分
public class PublishDao {

    //展示 全部数据的信息
    public List<Publish> listPublish() throws Exception {
        //		获得数据库链接
        Connection connection = JDBCUtils.getConnection();
//        String sql = "insert into users (username,password) values(?,?)";
        String sql = "select * from publish";
//		使用PreparedStatement执行SQL语句可以防止SQL注入
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
//		将结果集封装到集合
        List<Publish> list = new ArrayList<>();
        while (rs.next()) {
            Publish publish = new Publish(rs.getInt("id"), rs.getString("title"), rs.getString("outline"),
                    rs.getString("content"), rs.getString("object_name"), rs.getInt("tags"),
                    rs.getInt("user_id"),
                    rs.getDate("create_time"),
                    rs.getDate("update_time"));
            list.add(publish);
        }
        return list;
    }

    // 新增文件
    public boolean createPublish(String title, String outline, String content, String objectName, int user_id, int tags) throws Exception {
        //		获得数据库链接
        Connection connection = JDBCUtils.getConnection();
//        String sql = "insert into users (username,password) values(?,?)";
        String sql = "insert into publish(title,outline,content,object_name,tags,user_id,create_time,update_time) values(?,?,?,?,?,?,now(),now());";
//		使用PreparedStatement执行SQL语句可以防止SQL注入
        PreparedStatement ps = connection.prepareStatement(sql);
        //		设置占位符的参数
        ps.setString(1, title);
        ps.setString(2, outline);
        ps.setString(3, content);
        ps.setString(4, objectName);
        ps.setInt(5, tags);
        ps.setInt(6, user_id);

        int i = ps.executeUpdate();
        return i > 0 ? true : false;
    }

    //修改 标签
    public boolean addTags() {
        Connection connection = JDBCUtils.getConnection();
        String sql = "";
        return true;
    }

    //通过 url 去获取 id
    public int getId(String url) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select id from publish where content = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, url);
        ResultSet rs = ps.executeQuery();
        rs.next();
        int id = rs.getInt("id");
        return id;
    }


    //分页查询  ， 页面 ，大小
    public List<Publish> listPage(int page, int pageSize) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select * from publish limit ?,?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, page * pageSize);
        ps.setInt(2, pageSize);
        ResultSet rs = ps.executeQuery();
        List<Publish> list = new ArrayList<>();
        while (rs.next()) {
            Publish publish = new Publish(rs.getInt("id"), rs.getString("title"), rs.getString("outline"),
                    rs.getString("content"), rs.getString("object_name"), rs.getInt("tags"),
                    rs.getInt("user_id"),
                    rs.getDate("create_time"),
                    rs.getDate("update_time"));
            list.add(publish);
        }
        return list;
    }

    // 按 用户 模糊 查询 , 分页
    public List<Publish> listPublishByUser(String userName, int page, int pageSize) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select * from publish where user_id in (select id from user where username like ? ) limit ?,?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, "%" + userName + "%");
        ps.setInt(2, page * pageSize);
        ps.setInt(3, pageSize);
        ResultSet rs = ps.executeQuery();
        List<Publish> list = new ArrayList<>();
        while (rs.next()) {
            Publish publish = new Publish(rs.getInt("id"), rs.getString("title"), rs.getString("outline"),
                    rs.getString("content"), rs.getString("object_name"), rs.getInt("tags"),
                    rs.getInt("user_id"),
                    rs.getDate("create_time"),
                    rs.getDate("update_time"));
            list.add(publish);
        }
        return list;
    }

    // 查询 总的 数据 用户 模糊 查询
    public int listPublishByUserCount(String userName) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select count(*) from publish where user_id in (select id from user where username like ? )";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, "%" + userName + "%");
        ResultSet rs = ps.executeQuery();
        int count = 0;
        while (rs.next()) {
            count = rs.getInt(1);
        }
        return count;
    }

    // 按 标签 模糊 查询
    public List<Publish> listPublishByTags(int tags, int page, int pageSize) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select * from publish where tags=? limit ?,?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, tags);
        ps.setInt(2, page * pageSize);
        ps.setInt(3, pageSize);
        ResultSet rs = ps.executeQuery();
        List<Publish> list = new ArrayList<>();
        while (rs.next()) {
            Publish publish = new Publish(rs.getInt("id"), rs.getString("title"), rs.getString("outline"),
                    rs.getString("content"), rs.getString("object_name"), rs.getInt("tags"),
                    rs.getInt("user_id"),
                    rs.getDate("create_time"),
                    rs.getDate("update_time"));
            list.add(publish);
        }
        return list;
    }

    // 查询 总标签
    public int listPublishByTagsCount(int tags) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select count(*) from publish where tags=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, tags);
        ResultSet rs = ps.executeQuery();
        int count = 0;
        while (rs.next()) {
            count = rs.getInt(1);
        }
        return count;
    }

    // 按 标题 模糊 查询
    public List<Publish> listPublishByTitle(String title, int page, int pageSize) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select * from publish where title like ? limit ?,?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, "%" + title + "%");
        ps.setInt(2, page * pageSize);
        ps.setInt(3, pageSize);
        ResultSet rs = ps.executeQuery();
        List<Publish> list = new ArrayList<>();
        while (rs.next()) {
            Publish publish = new Publish(rs.getInt("id"), rs.getString("title"), rs.getString("outline"),
                    rs.getString("content"), rs.getString("object_name"), rs.getInt("tags"),
                    rs.getInt("user_id"),
                    rs.getDate("create_time"),
                    rs.getDate("update_time"));
            list.add(publish);
        }
        return list;
    }

    //查询 总的 标题 模糊
    public int listPublishByTitleCount(String title) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select count(*) from publish where title like ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, "%" + title + "%");
        ResultSet rs = ps.executeQuery();
        int count = 0;
        while (rs.next()) {
            count = rs.getInt(1);
        }
        return count;
    }

    //通过 概要模糊查询
    public List<Publish> listPublishByOutline(String outline, int page, int pageSize) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select * from publish where outline like ? limit ?,?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, "%" + outline + "%");
        ps.setInt(2, page * pageSize);
        ps.setInt(3, pageSize);
        ResultSet rs = ps.executeQuery();
        List<Publish> list = new ArrayList<>();
        while (rs.next()) {
            Publish publish = new Publish(rs.getInt("id"), rs.getString("title"), rs.getString("outline"),
                    rs.getString("content"), rs.getString("object_name"), rs.getInt("tags"),
                    rs.getInt("user_id"),
                    rs.getDate("create_time"),
                    rs.getDate("update_time"));
            list.add(publish);
        }
        return list;
    }

    // 查询总的概要
    public int listPublishByOutlineCount(String outline) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select count(*) from publish where outline like ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, "%" + outline + "%");
        ResultSet rs = ps.executeQuery();
        int count = 0;
        while (rs.next()) {
            count = rs.getInt(1);
        }
        return count;
    }

    // 查看 用户 点赞的文章
    public List<Publish> listPublishByUserLike(int userId, int page, int pageSize) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select * from publish where id in (select publish_id from functionc where likes=1 and user_id=?) limit ?,?;";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, userId);
        ps.setInt(2, page * pageSize);
        ps.setInt(3, pageSize);
        ResultSet rs = ps.executeQuery();
        List<Publish> list = new ArrayList<>();
        while (rs.next()) {
            Publish publish = new Publish(rs.getInt("id"), rs.getString("title"), rs.getString("outline"),
                    rs.getString("content"), rs.getString("object_name"), rs.getInt("tags"),
                    rs.getInt("user_id"),
                    rs.getDate("create_time"),
                    rs.getDate("update_time"));
            list.add(publish);
        }
        return list;
    }

    // 查询 总的 点赞
    public int listPublishByUserLikeCount(int userId) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select count(*) from publish where id in (select publish_id from functionc where user_id=? and likes=1)";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, userId);
        ResultSet rs = ps.executeQuery();
        int count = 0;
        while (rs.next()) {
            count = rs.getInt(1);
        }
        return count;
    }

    // 查询 用户 总的收藏
    public List<Publish> listPublishByUserCollect(int userId, int page, int pageSize) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select * from publish where id in (select publish_id from functionc where user_id=? and collect=1) limit ?,?;";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, userId);
        ps.setInt(2, page * pageSize);
        ps.setInt(3, pageSize);
        ResultSet rs = ps.executeQuery();
        List<Publish> list = new ArrayList<>();
        while (rs.next()) {
            Publish publish = new Publish(rs.getInt("id"), rs.getString("title"), rs.getString("outline"),
                    rs.getString("content"), rs.getString("object_name"), rs.getInt("tags"),
                    rs.getInt("user_id"),
                    rs.getDate("create_time"),
                    rs.getDate("update_time"));
            list.add(publish);
        }
        return list;
    }

    // 用户 总的 收藏数
    public int listPublishByUserCollectCount(int userId) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select count(*) from publish where id in (select publish_id from functionc where user_id=? and collect=1)";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, userId);
        ResultSet rs = ps.executeQuery();
        int count = 0;
        while (rs.next()) {
            count = rs.getInt(1);
        }
        return count;
    }

    // 查询 用户 转发的文章
    public List<Publish> listPublishByUserRetransmission(int userId, int page, int pageSize) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select * from publish where id in (select publish_id from functionc where user_id=? and retransmission!=0) limit ?,?;";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, userId);
        ps.setInt(2, page * pageSize);
        ps.setInt(3, pageSize);
        ResultSet rs = ps.executeQuery();
        List<Publish> list = new ArrayList<>();
        while (rs.next()) {
            Publish publish = new Publish(rs.getInt("id"), rs.getString("title"), rs.getString("outline"),
                    rs.getString("content"), rs.getString("object_name"), rs.getInt("tags"),
                    rs.getInt("user_id"),
                    rs.getDate("create_time"),
                    rs.getDate("update_time"));
            list.add(publish);
        }
        return list;
    }

    // 查询用户 总的 转发数量
    public int listPublishByUserRetransmissionCount(int userId) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select count(*) from publish where id in (select publish_id from functionc where user_id=? and retransmission!=0)";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, userId);
        ResultSet rs = ps.executeQuery();
        int count = 0;
        while (rs.next()) {
            count = rs.getInt(1);
        }
        return count;
    }

    // 查看评论的文章
    public List<Publish> listPublishByComment(int userId, int page, int pageSize) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select * from publish where id in (select publish_id from comment where user_id=?) limit ?,?;";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, userId);
        ps.setInt(2, page * pageSize);
        ps.setInt(3, pageSize);
        ResultSet rs = ps.executeQuery();
        List<Publish> list = new ArrayList<>();
        while (rs.next()) {
            Publish publish = new Publish(rs.getInt("id"), rs.getString("title"), rs.getString("outline"),
                    rs.getString("content"), rs.getString("object_name"), rs.getInt("tags"),
                    rs.getInt("user_id"),
                    rs.getDate("create_time"),
                    rs.getDate("update_time"));
            list.add(publish);
        }
        return list;
    }

    // 通过主键id去 获取 url
    public List<String> showIndexView(int index) throws SQLException {
        //		获得数据库链接
        Connection connection = JDBCUtils.getConnection();
        String sql = "select content,object_name from publish where id=?";
//		使用PreparedStatement执行SQL语句可以防止SQL注入
        PreparedStatement ps = connection.prepareStatement(sql);
        //		设置占位符的参数
        ps.setInt(1, index);

        ResultSet rs = ps.executeQuery();
//        System.out.println(rs.next()+"1111"+rs.getString(1));
        rs.next();
        List<String> ls = new ArrayList<>();
        ls.add(rs.getString("content"));
        ls.add(rs.getString("object_name"));
        return ls;
    }

    // 用户 发布文章 个数
    public int listPublishByUserCount(int userId) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select count(*) from publish where user_id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, userId);
        ResultSet rs = ps.executeQuery();
        int count = 0;
        while (rs.next()) {
            count = rs.getInt(1);
        }
        return count;
    }

    // 查看用户 发布的 文章
    public List<Publish> listPublishByUser(int userId, int page, int pageSize) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select * from publish where user_id=? limit ?,?;";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, userId);
        ps.setInt(2, page * pageSize);
        ps.setInt(3, pageSize);
        ResultSet rs = ps.executeQuery();
        List<Publish> list = new ArrayList<>();
        while (rs.next()) {
            Publish publish = new Publish(rs.getInt("id"), rs.getString("title"), rs.getString("outline"),
                    rs.getString("content"), rs.getString("object_name"), rs.getInt("tags"),
                    rs.getInt("user_id"),
                    rs.getDate("create_time"),
                    rs.getDate("update_time"));
            list.add(publish);
        }
        return list;
    }

    // 更具 文章 id去删除文章
    public void deletePublishById(int id) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "delete from publish where id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, id);
        ps.executeUpdate();
    }

    // 查看 通过 文章 id 去获取 用户 id
    public int getUserIdByPublishId(int publishId) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select user_id from publish where id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, publishId);
        ResultSet rs = ps.executeQuery();
        int userId = 0;
        while (rs.next()) {
            userId = rs.getInt("user_id");
        }
        return userId;
    }
}
